# -*- coding:utf-8 -*-
import datetime
from . import db

STATE_CREATE = 0
STATE_CHIEF_PUSH = 1
STATE_BRANCH_RECEIVE = 2
STATE_BRANCH_PUSH = 3
STATE_ASSESS_RECEIVE = 4
STATE_ASSESS_PUSH = 5
STATE_INSURANCE_RECEIVE = 6
STATE_FINISHED = 7

TYPE_SELF = 0
TYPE_ASSESS = 1

def addCase(branchCompanyId, _type, address, reason, insurance_time, value):
    return db.insert('cases', branch_company_id  = branchCompanyId, type = _type, address = address, reason = reason, insurance_time = insurance_time, value = value)

def getById(caseId):
    result = db.query('SELECT * FROM cases WHERE id = $caseId', locals())
    if result:
        return result[0]
    return None

def modifyCaseStatus(caseId, status):
    return db.update('cases', 'id = $caseId', locals(), status = status)

def modifyCaseContent(caseId, address, reason, insurance_time, value):
    return db.update('cases', 'id = $caseId', locals(), address = address, reason = reason, insurance_time = insurance_time, value = value)

def setCaseAssessor(caseId, assessorId):
    return db.update('cases', 'id = $caseId', locals(), assessor_id  = assessorId)

def updateCaseActionTime(caseId, t):
    return db.update('cases', 'id = $caseId', locals(), end_time = t)

def finishedCases(branchCompanyId = None):
    if branchCompanyId:
        return list(db.query('SELECT cases.id, cases.type, insurance_time, address, company.name as assessor, end_time, value, status FROM cases LEFT JOIN company ON company.id = cases.assessor_id WHERE branch_company_id  = $branchCompanyId AND status = 7 ORDER BY end_time DESC', locals()))
    else:
        return list(db.query('SELECT cases.id, cases.type, insurance_time, address, company.name as assessor, end_time, value, status FROM cases LEFT JOIN company ON company.id = cases.assessor_id WHERE status = 7 ORDER BY end_time DESC', locals()))

def unfinishedCases(branchCompanyId = None):
    if branchCompanyId:
        return list(db.query('SELECT cases.id, cases.type, insurance_time, address, company.name as assessor, end_time, value, status FROM cases LEFT JOIN company ON company.id = cases.assessor_id WHERE branch_company_id  = $branchCompanyId AND status <> 7 ORDER BY end_time DESC', locals()))
    else:
        return list(db.query('SELECT cases.id, cases.type, insurance_time, address, company.name as assessor, end_time, value, status FROM cases LEFT JOIN company ON company.id = cases.assessor_id WHERE status <> 7 ORDER BY end_time DESC', locals()))

def setAssess(caseId, assessId):
    return db.update('cases', 'id = $caseId', locals(), assessor_id = assessId)

def getLastestOperateTime():
    result = db.query('SELECT end_time FROM cases ORDER BY end_time DESC LIMIT 1')
    if result:
        return result[0]['end_time']
    return datetime.datetime.now()

def getUnfinishedCasesValueSum(_type = TYPE_SELF, branchCompanyId = None):
    if branchCompanyId:
        value = db.query('SELECT sum(value) as s FROM cases WHERE branch_company_id = $branchCompanyId AND type = $_type  AND status <> 7', locals())[0]['s'] or 0
    else:
        value = db.query('SELECT sum(value) as s FROM cases WHERE type = $_type AND status <> 7', locals())[0]['s'] or 0
    return int(value)

def getFinishedCasesValueSum(_type = TYPE_SELF, branchCompanyId = None):
    if branchCompanyId:
        value =  db.query('SELECT sum(value) as s FROM cases WHERE branch_company_id = $branchCompanyId AND type = $_type AND status = 7', locals())[0]['s'] or 0
    else:
        value = db.query('SELECT sum(value) as s FROM cases WHERE type = $_type AND status = 7', locals())[0]['s'] or 0
    return int(value)

def getUnfinishedCasesCount(_type = TYPE_SELF, branchCompanyId = None):
    if branchCompanyId:
        return db.query('SELECT count(*) FROM cases WHERE branch_company_id = $branchCompanyId AND type = $_type AND status <> 7', locals())[0]['count(*)']
    else:
        return db.query('SELECT count(*) FROM cases WHERE type = $_type AND status <> 7', locals())[0]['count(*)']

def getFinishedCasesCount(_type = TYPE_SELF, branchCompanyId = None):
    if branchCompanyId:
        return db.query('SELECT count(*) FROM cases WHERE branch_company_id = $branchCompanyId AND type = $_type AND status = 7', locals())[0]['count(*)']
    else:
        return db.query('SELECT count(*) FROM cases WHERE type = $_type AND status = 7', locals())[0]['count(*)']

def clear():
    db.query('delete from cases')
